﻿using Ferhrosa.Wpf;
using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.IO;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace Migrador {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class Principal : Window {
        public Principal() {
            InitializeComponent();
        }

        private void btnMigrar_Click(object sender, RoutedEventArgs e) {
            if (!File.Exists(tbAccess.Text)) {
                MessageBox.Show("O caminho informado para o arquivo do banco de dados Access é inválido.", "Erro (Access)", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }

            var conAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tbAccess.Text + ";User Id=admin;Password=;");

            try {
                conAccess.Open();
            } catch (Exception ex) {
                MessageBox.Show("Não foi possível fazer a conexão com o banco de dados Access informado.\n\n(" + ex.Message + ")", "Erro (Access)", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }


            var conSql = new SqlConnection("Data Source=" + tbSqlServidor.Text + ";Initial Catalog=" + tbSqlBanco.Text + ";User Id=" + tbSqlUsuario.Text + ";Password=" + tbSqlSenha.Password + ";");

            try {
                conSql.Open();
            } catch (Exception ex) {
                MessageBox.Show("Não foi possível fazer a conexão com o banco de dados SQL Server informado.\n\n(" + ex.Message + ")", "Erro (SQL Server)", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }


            var comAccess = new OleDbCommand("SELECT COUNT(cod_ser) AS Total FROM servico", conAccess);
            int servicoTotal = (int)comAccess.ExecuteScalar();

            comAccess.CommandText = "SELECT COUNT(cod_eqp) AS Total FROM servicoeqp";
            int servicoeqpTotal = (int)comAccess.ExecuteScalar();

            progressBar.Maximum = servicoTotal + servicoeqpTotal + 3;
            progressBar.Value = 0;
            progressBar.Refresh();


            var comSql = new SqlCommand(String.Empty, conSql);
            var sql = new StringBuilder();

            comSql.CommandText = "BACKUP DATABASE [MecSom] TO  [MecSom] WITH NOFORMAT, NOINIT,  NAME = N'Migração - " + DateTime.Now.ToString() + "', SKIP, NOREWIND, NOUNLOAD,  STATS = 10";
            try {
                comSql.ExecuteNonQuery();
            } catch (Exception ex) {
                MessageBox.Show("Não foi possível fazer o backup do banco de dados SQL Server.\n\n(" + ex.Message + ")", "Erro (SQL Server)", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
            progressBar.Value++;
            progressBar.Refresh();

            comSql.CommandText = "DELETE Servico";
            comSql.ExecuteNonQuery();
            progressBar.Value++;
            progressBar.Refresh();

            comSql.CommandText = "DELETE Cliente";
            comSql.ExecuteNonQuery();
            progressBar.Value++;
            progressBar.Refresh();

            comSql.CommandText = "SET LANGUAGE Brazilian";
            comSql.ExecuteNonQuery();


            comAccess.CommandText = "SELECT * FROM servico ORDER BY cod_ser WHERE ativo = 'S'";
            var rdrAccess = comAccess.ExecuteReader();

            comSql.CommandText = "SET IDENTITY_INSERT Cliente ON";
            comSql.ExecuteNonQuery();

            var cod_ser = 0;

            while (rdrAccess.Read()) {
                cod_ser = (int)rdrAccess["cod_ser"];
                var data = (DateTime)rdrAccess["data"];
                var cliente = (DBNull.Value.Equals(rdrAccess["cliente"]) ? String.Empty : rdrAccess["cliente"].ToString().Replace("'", "''"));
                var clientetel1 = (DBNull.Value.Equals(rdrAccess["clientetel1"]) ? String.Empty : rdrAccess["clientetel1"].ToString().Replace("'", "''"));
                var clientetel2 = (DBNull.Value.Equals(rdrAccess["clientetel2"]) ? String.Empty : rdrAccess["clientetel2"].ToString().Replace("'", "''"));
                var clientetel3 = (DBNull.Value.Equals(rdrAccess["clientetel3"]) ? String.Empty : rdrAccess["clientetel3"].ToString().Replace("'", "''"));
                var contato = (DBNull.Value.Equals(rdrAccess["contato"]) ? String.Empty : rdrAccess["contato"].ToString().Replace("'", "''"));
                var contatotel1 = (DBNull.Value.Equals(rdrAccess["contatotel1"]) ? String.Empty : rdrAccess["contatotel1"].ToString().Replace("'", "''"));
                var contatotel2 = (DBNull.Value.Equals(rdrAccess["contatotel2"]) ? String.Empty : rdrAccess["contatotel2"].ToString().Replace("'", "''"));
                var contatotel3 = (DBNull.Value.Equals(rdrAccess["contatotel3"]) ? String.Empty : rdrAccess["contatotel3"].ToString().Replace("'", "''"));

                sql.Remove(0, sql.Length);
                sql.AppendLine("INSERT Cliente (CodCliente, DataInclusao, Nome, Telefone1, Telefone2, Telefone3, Contato, Telefone4, Telefone5, Telefone6)");
                sql.AppendLine("VALUES (");
                sql.AppendLine(cod_ser.ToString() + ",");
                sql.AppendLine("'" + data.ToString("dd/MM/yyyy") + "',");
                sql.AppendLine("'" + cliente + "',");
                sql.AppendLine((String.IsNullOrEmpty(clientetel1.Trim()) ? "NULL" : "'" + clientetel1 + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(clientetel2.Trim()) ? "NULL" : "'" + clientetel2 + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(clientetel3.Trim()) ? "NULL" : "'" + clientetel3 + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(contato.Trim()) ? "NULL" : "'" + contato + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(contatotel1.Trim()) ? "NULL" : "'" + contatotel1 + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(contatotel2.Trim()) ? "NULL" : "'" + contatotel2 + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(contatotel3.Trim()) ? "NULL" : "'" + contatotel3 + "'"));
                sql.AppendLine(")");

                comSql.CommandText = sql.ToString();
                comSql.ExecuteNonQuery();

                progressBar.Value++;
                progressBar.Refresh();
            }

            comSql.CommandText = "SET IDENTITY_INSERT Cliente OFF";
            comSql.ExecuteNonQuery();

            comSql.CommandText = "DBCC CHECKIDENT (Cliente, RESEED, " + cod_ser.ToString() + ")";
            comSql.ExecuteNonQuery();


            comAccess.CommandText = "SELECT * FROM servicoeqp ORDER BY cod_eqp";
            rdrAccess.Close();
            rdrAccess = comAccess.ExecuteReader();

            comSql.CommandText = "SET IDENTITY_INSERT Servico ON";
            comSql.ExecuteNonQuery();

            var cod_eqp = 0;

            while (rdrAccess.Read()) {
                cod_eqp = (int)rdrAccess["cod_eqp"];
                cod_ser = (int)rdrAccess["cod_ser"];

                var tipo = (DBNull.Value.Equals(rdrAccess["tipo"]) ? String.Empty : rdrAccess["tipo"].ToString().Replace("'", "''").ToUpper());
                var marca = (DBNull.Value.Equals(rdrAccess["marca"]) ? String.Empty : rdrAccess["marca"].ToString().Replace("'", "''").ToUpper());
                var modelo = (DBNull.Value.Equals(rdrAccess["modelo"]) ? String.Empty : rdrAccess["modelo"].ToString().Replace("'", "''").ToUpper());
                var defeito = (DBNull.Value.Equals(rdrAccess["defeito"]) ? String.Empty : rdrAccess["defeito"].ToString().Replace("'", "''"));
                var acoes = (DBNull.Value.Equals(rdrAccess["acoes"]) ? String.Empty : rdrAccess["acoes"].ToString().Replace("'", "''"));
                var orcamento = (DBNull.Value.Equals(rdrAccess["orcamento"]) ? 0 : (int)Math.Floor((float)rdrAccess["orcamento"] * 100));
                var pago = (DBNull.Value.Equals(rdrAccess["pago"]) ? false : (bool)rdrAccess["pago"]);
                var estado = (DBNull.Value.Equals(rdrAccess["estado"]) ? String.Empty : rdrAccess["estado"].ToString().Replace("'", "''"));
                var estadoorcamento = (DBNull.Value.Equals(rdrAccess["estadoorcamento"]) ? String.Empty : ((DateTime)rdrAccess["estadoorcamento"]).ToString("dd/MM/yyyy"));
                var estadocancelado = (DBNull.Value.Equals(rdrAccess["estadocancelado"]) ? String.Empty : ((DateTime)rdrAccess["estadocancelado"]).ToString("dd/MM/yyyy"));
                var estadoesperandopecas = (DBNull.Value.Equals(rdrAccess["estadoesperandopecas"]) ? String.Empty : ((DateTime)rdrAccess["estadoesperandopecas"]).ToString("dd/MM/yyyy"));
                var estadoaprovado = (DBNull.Value.Equals(rdrAccess["estadoaprovado"]) ? String.Empty : ((DateTime)rdrAccess["estadoaprovado"]).ToString("dd/MM/yyyy"));
                var estadopronto = (DBNull.Value.Equals(rdrAccess["estadopronto"]) ? String.Empty : ((DateTime)rdrAccess["estadopronto"]).ToString("dd/MM/yyyy"));
                var estadoentregue = (DBNull.Value.Equals(rdrAccess["estadoentregue"]) ? String.Empty : ((DateTime)rdrAccess["estadoentregue"]).ToString("dd/MM/yyyy"));
                var obs = (DBNull.Value.Equals(rdrAccess["obs"]) ? String.Empty : rdrAccess["obs"].ToString().Replace("'", "''"));

                sql.Remove(0, sql.Length);
                sql.AppendLine("INSERT Servico (CodServico, CodCliente, Tipo, Marca, Modelo, Defeito, Acoes, Orcamento, Pago, Situacao, SituacaoOrcamento, SituacaoCancelado, SituacaoEsperandoPecas, SituacaoAprovado, SituacaoPronto, SituacaoEntregue, Observacoes)");
                sql.AppendLine("VALUES (");
                sql.AppendLine(cod_eqp.ToString() + ",");
                sql.AppendLine(cod_ser.ToString() + ",");
                sql.AppendLine((String.IsNullOrEmpty(tipo.Trim()) ? "NULL" : "'" + tipo + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(marca.Trim()) ? "NULL" : "'" + marca + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(modelo.Trim()) ? "NULL" : "'" + modelo + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(defeito.Trim()) ? "NULL" : "'" + defeito + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(acoes.Trim()) ? "NULL" : "'" + acoes + "'") + ",");
                sql.AppendLine((orcamento == 0 ? "NULL" : orcamento.ToString()) + ",");
                sql.AppendLine((pago ? "0" : "1") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estado.Trim()) ? "NULL" : "'" + estado + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estadoorcamento.Trim()) ? "NULL" : "'" + estadoorcamento + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estadocancelado.Trim()) ? "NULL" : "'" + estadocancelado + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estadoesperandopecas.Trim()) ? "NULL" : "'" + estadoesperandopecas + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estadoaprovado.Trim()) ? "NULL" : "'" + estadoaprovado + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estadopronto.Trim()) ? "NULL" : "'" + estadopronto + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(estadoentregue.Trim()) ? "NULL" : "'" + estadoentregue + "'") + ",");
                sql.AppendLine((String.IsNullOrEmpty(obs.Trim()) ? "NULL" : "'" + obs + "'"));
                sql.AppendLine(")");

                comSql.CommandText = sql.ToString();
                comSql.ExecuteNonQuery();

                progressBar.Value++;
                progressBar.Refresh();
            }

            comSql.CommandText = "SET IDENTITY_INSERT Servico OFF";
            comSql.ExecuteNonQuery();

            comSql.CommandText = "DBCC CHECKIDENT (Servico, RESEED, " + cod_eqp.ToString() + ")";
            comSql.ExecuteNonQuery();


            MessageBox.Show("Migração concluída com sucesso!");

            conAccess.Close();
            conAccess.Dispose();

            conSql.Close();
            conSql.Dispose();
        }


        private void btnAccess_Click(object sender, RoutedEventArgs e) {
            var dlg = new OpenFileDialog();
            dlg.Multiselect = false;
            dlg.FileName = File.Exists(tbAccess.Text) ? tbAccess.Text : String.Empty;
            if (dlg.ShowDialog(this).Value) tbAccess.Text = dlg.FileName;
        }


        private void Window_Loaded(object sender, RoutedEventArgs e) {
            tbAccess.Text = Registro.GetValue("Access", String.Empty).ToString();
            tbSqlServidor.Text = Registro.GetValue("SqlServidor", String.Empty).ToString();
            tbSqlBanco.Text = Registro.GetValue("SqlBanco", String.Empty).ToString();
            tbSqlUsuario.Text = Registro.GetValue("SqlUsuario", String.Empty).ToString();
        }
        private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e) {
            Registro.SetValue("Access", tbAccess.Text);
            Registro.SetValue("SqlServidor", tbSqlServidor.Text);
            Registro.SetValue("SqlBanco", tbSqlBanco.Text);
            Registro.SetValue("SqlUsuario", tbSqlUsuario.Text);
        }

        private RegistryKey _registro;
        private RegistryKey Registro {
            get {
                if (null == _registro) _registro = Registry.CurrentUser.CreateSubKey("Ferhrosa\\MecSom\\Migrador");
                return _registro;
            }
        }


    }
}
